/**
 * 
 */
package com.itinsys.enma.service.impl;

import java.text.ParseException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.List;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import com.itinsys.enma.dao.DBTemplate;
import com.itinsys.enma.dao.ReservationDAO;
import com.itinsys.enma.dao.hibernate.DBObject;
import com.itinsys.enma.domain.DailyReservation;
import com.itinsys.enma.domain.Period;
import com.itinsys.enma.domain.Planning;
import com.itinsys.enma.domain.PlanningReservation;
import com.itinsys.enma.domain.Status;
import com.itinsys.enma.domain.dto.ConflictReservationDTO;
import com.itinsys.enma.domain.dto.DailyReservationDTO;
import com.itinsys.enma.domain.dto.PlanningReservationDTO;
import com.itinsys.enma.domain.mapper.ConflictReservationDTOMapper;
import com.itinsys.enma.domain.response.DailyReservationResponse;
import com.itinsys.enma.exception.planning.PlanningExistsException;
import com.itinsys.enma.exception.reservation.ReservationExistsException;
import com.itinsys.enma.manager.PlanningManager;
import com.itinsys.enma.manager.ReservationDTOManager;
import com.itinsys.enma.manager.ResponseManager;
import com.itinsys.enma.service.ConflictCheckerService;

/**
 * @author pferraz
 *
 */
public class ConflictCheckerServiceImpl implements ConflictCheckerService {
	private static Logger logger = Logger.getLogger("service");
	
	@Autowired
	private ResponseManager responseManager;
	
	@Autowired
	private PlanningManager planningManager;
	
	@Autowired
	private ReservationDAO reservationDAO;
	@Autowired
	private ReservationDTOManager reservationDTOManager;

	@SuppressWarnings("unchecked")
	@Override
	public void executeLocalCheck(PlanningReservation r,PlanningReservationDTO rDTO, String schoolId) {
		BasicDataSource dataSource=DBTemplate.mysqlDataSource;
		String strSTime=r.getStartTime().toString();
		String strETime=r.getEndTime().toString();
		try{
			List<ConflictReservationDTO> conflictDTO=((List<ConflictReservationDTO>)new NamedParameterJdbcTemplate(dataSource).query( 
					"SELECT r.id,p.career,p.careerYear,r.description,r.sGroup,r.startTime,r.endTime"+
					 " FROM reservations r JOIN plannings p ON r.planning_id = p.id"+
					 " WHERE p.schoolId='"+schoolId+"' and (p.status='IN_PROCESS' or p.status='WAITING_CONFIRMATION' or p.status='IN_CONFLICT' or p.status='CONFIRMED')"+
					 " and r.day ="+r.getDay()+" and"+
					 " ('"+strSTime+"' <= r.endTime AND '"+strETime+"' >= r.startTime )"+
					 " and r.environment_id = '"+r.getEnvironment().getId()+"' and r.active=true and r.id!="+r.getId(),
					new MapSqlParameterSource(),
					new ConflictReservationDTOMapper()));
			
			//Como va a chequear con todos se borran los actuales y se agrega si queda algun conflicto
			DBObject.txInsertDelete("DELETE FROM inconflict where conflictId="+r.getId()+" or conflictWithId="+r.getId());
			if(!conflictDTO.isEmpty()){
				periodConflictFilter(conflictDTO,r);
				for(ConflictReservationDTO confl:conflictDTO){
					DBObject.txExecuteQuery("INSERT INTO inconflict(conflictId,conflictWithId) VALUES("+rDTO.getId()+","+confl.getId()+")");
					DBObject.txExecuteQuery("UPDATE plannings set status='IN_CONFLICT' WHERE status<>'CONFIRMED' and id=(SELECT planning_id FROM reservations WHERE id="+String.valueOf(confl.getId())+")");
					
				}
				if(!conflictDTO.isEmpty()){
					DBObject.txExecuteQuery("UPDATE plannings set status='IN_CONFLICT' WHERE id="+String.valueOf(r.getPlanning().getId()));
				}else{
					conflictDTO=null;
					DBObject.txExecuteQuery("UPDATE plannings set status='IN_PROCESS' WHERE id="+String.valueOf(r.getPlanning().getId()));
				}
				rDTO.setConflictsResDTO(conflictDTO);
				
			}
			
			DBObject.txExecuteQuery("UPDATE plannings p set status='WAITING_CONFIRMATION' WHERE status='IN_CONFLICT' and sendDate is not null and"
					+ " p.id not in (SELECT distinct(r.planning_id) as id FROM reservations r JOIN inconflict i ON i.conflictWithId=r.id or i.conflictId=r.id)");
			DBObject.txExecuteQuery("UPDATE plannings p set status='IN_PROCESS' WHERE status='IN_CONFLICT' and sendDate is null and"
					+ " p.id not in (SELECT distinct(r.planning_id) as id FROM reservations r JOIN inconflict i ON i.conflictWithId=r.id or i.conflictId=r.id)");
		}	catch(DataAccessException e){
			logger.error("Error al buscar conflictos", e);
		} 
		
	}
	
	/***
	 * De los conflictos encontrados filtra aquellos que sigan en conflicto con los periodos de la reserva
	 * @param conflictDTO
	 * @param r
	 */
	private void periodConflictFilter(List<ConflictReservationDTO> conflictDTOs, PlanningReservation r) {
		try {
			List<ConflictReservationDTO> conflictToRemove=new ArrayList<ConflictReservationDTO>();
			for(ConflictReservationDTO conflict:conflictDTOs){
				for(Period period:r.getReservationPeriods()){
					PlanningReservation reserv;
					reserv = reservationDAO.getById(conflict.getId());
					boolean isConflict=false;
					for(Period resPeriod:reserv.getReservationPeriods()){
						if(!(period.getStartDate().getTime()<=resPeriod.getEndDate().getTime()
						  && period.getEndDate().getTime()>=resPeriod.getStartDate().getTime())){
							conflictToRemove.add(conflict);
						}else{
							conflictToRemove.remove(conflict);
							isConflict=true;
							break;//Si alguno me da false me voy o si es uno de los ultimos quito de la lista el conflicto a remover para que tenga conflicto
						}
				  }
				if(isConflict)
					break;
				}
			}
			for(ConflictReservationDTO toRemove : conflictToRemove){
				conflictDTOs.remove(toRemove);
			}
		} catch (ReservationExistsException e) {
			logger.error(e.getMessage());
		}
		
		
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<ConflictReservationDTO> executeGlobalCheck(List<PlanningReservation> reservations) {
		BasicDataSource dataSource=DBTemplate.mysqlDataSource;
		List<ConflictReservationDTO> conflictDTOs=new ArrayList<ConflictReservationDTO>();
		try{
			for(PlanningReservation r:reservations){
				PlanningReservationDTO rDTO=reservationDTOManager.reservationToEventDTO(r);
				String strSTime=r.getStartTime().toString();
				String strETime=r.getEndTime().toString();
				List<ConflictReservationDTO> conflictDTO=((List<ConflictReservationDTO>)new NamedParameterJdbcTemplate(dataSource).query( 
						"SELECT r.id,p.career,p.careerYear,r.description,r.sGroup,r.startTime,r.endTime,r.planning_id"+
						 " FROM reservations r JOIN plannings p ON r.planning_id = p.id"+
						 " WHERE (p.status = '"+Status.WAITING_CONFIRMATION.toString()+"' or p.status = '"+Status.CONFIRMED.toString()+"' or p.status = '"+Status.IN_CONFLICT.toString()+"') and r.day ="+r.getDay()+" and"+
						 " ('"+strSTime+"' <= r.endTime AND '"+strETime+"' >= r.startTime )"+
						 " and r.environment_id = '"+r.getEnvironment().getId()+"' and r.active=true and r.id!="+r.getId(),
						new MapSqlParameterSource(),
						new ConflictReservationDTOMapper()));
				//Como va a chequear con todos se borran los actuales y se agrega si queda algun conflicto
				DBObject.txInsertDelete("DELETE FROM inconflict where conflictId="+r.getId()+" or conflictWithId="+r.getId());
				if(!conflictDTO.isEmpty()){
					periodConflictFilter(conflictDTO,r);
					conflictDTOs.addAll(conflictDTO);
					for(ConflictReservationDTO confl:conflictDTO)
						DBObject.txExecuteQuery("INSERT INTO inconflict(conflictId,conflictWithId) VALUES("+rDTO.getId()+","+confl.getId()+")");
					if(!conflictDTO.isEmpty()){
						rDTO.setConflictsResDTO(conflictDTO);
					}
				}
			}
		}	catch(DataAccessException e){
			logger.error("Error al buscar conflictos", e);
		} catch (ReservationExistsException e) {
			logger.info(e.getMessage());
		} catch (ParseException e) {
			logger.error("Error en parseo", e);
		} 
		return conflictDTOs;
	}

	@Override
	public DailyReservationResponse executeDailyConflictCheck(DailyReservation r,DailyReservationDTO dRDTO) {
		DailyReservationResponse response=new DailyReservationResponse();
		responseManager.prepareResponse(response, DailyReservationResponse.OK);
		
		
		ConflictReservationDTO confDTO=getByPlanningOverlap(r);
		if(confDTO==null){
			try {
				DailyReservation dR=reservationDAO.getByOverlap(r);
				if(r.getId()!=dR.getId()){
					responseManager.prepareResponse(response, DailyReservationResponse.DAILY_RESERVATION_EXISTS);
					response.setMessage(response.getMessage().replace("(", dR.getStartTime().toString().substring(0, 5)));
					response.setMessage(response.getMessage().replace(")", dR.getEndTime().toString().substring(0, 5)));
					response.setMessage(response.getMessage().replace("#", dR.getUser().getUsername()));
				}
			} catch (ReservationExistsException e) {
				
			}
		}else{
			Planning plann=null;
			try {
				plann = planningManager.getPlanningById(confDTO.getPlannId());
				responseManager.prepareResponse(response, DailyReservationResponse.DAILY_WITH_PLANNING_CONFLICT);
				response.setMessage(response.getMessage().replace("#", plann.getUser().getUsername()));
				response.setMessage(response.getMessage().replace(";", confDTO.getCareer()));
				response.setMessage(response.getMessage().replace("&", confDTO.getDescription()));
				response.setMessage(response.getMessage().replace("(", confDTO.getStart_date().toString().substring(11, 16)));
				response.setMessage(response.getMessage().replace(")", confDTO.getEnd_date().toString().substring(11, 16)));
			} catch (PlanningExistsException e) {
				logger.error(e.getMessage());
			}
			
			
		}
		return response;
		
	}

	@SuppressWarnings("unchecked")
	private ConflictReservationDTO getByPlanningOverlap(DailyReservation r) {
		BasicDataSource dataSource=DBTemplate.mysqlDataSource;
		Calendar tmpCal = new GregorianCalendar();
		tmpCal.setTimeInMillis(r.getStartDate().getTime());
		int day=tmpCal.get(Calendar.DAY_OF_WEEK)-1;	
        
		String strSTime=r.getStartTime().toString();
		String strETime=r.getEndTime().toString();
		List<ConflictReservationDTO> conflictDTO=new ArrayList<ConflictReservationDTO>();
		try{
			conflictDTO=((List<ConflictReservationDTO>)new NamedParameterJdbcTemplate(dataSource).query( 
					"SELECT r.id,p.career,p.careerYear,r.description,r.sGroup,r.startTime,r.endTime,r.planning_id"+
					 " FROM reservations r JOIN plannings p ON r.planning_id = p.id JOIN reservation_periods rp ON r.id=rp.reservation_id" +
					 " JOIN periods per ON rp.period_id=per.id"+
					 " WHERE p.status='"+Status.CONFIRMED.toString()+"' and r.day ="+day+" and"+
					 " ('"+r.getStartDate().toString()+"' <= per.endDate AND '"+r.getStartDate().toString()+"' >= per.startDate ) and ('"+strSTime+"' <= r.endTime AND '"+strETime+"' >= r.startTime )"+
					 " and r.environment_id = '"+r.getEnvironment().getId()+"' and r.active=true and r.id!="+r.getId(),
					new MapSqlParameterSource(),
					new ConflictReservationDTOMapper()));
		}	catch(DataAccessException e){
			logger.error("Error al buscar conflictos", e);
		} 
		return (conflictDTO.isEmpty())?null:conflictDTO.get(0);
	}

	

}
